﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;
using System.Data.SqlClient;
using System.ServiceModel;
using System.Xml;
namespace saveimagetodatabase
{
    class SqlQuery
    {
        private string _error;
        public string Error
        {
            get { return _error; }
            set { _error = value; }
        }
        SqlConnection con;
        SqlCommand command;
       
        public SqlQuery()
        {

            
            con = new SqlConnection();
            String connectstring = "Data Source=VANHUNGBKCBG;Initial Catalog=sinhvien;Persist Security Info=True;User ID=sa;Password=vanhungbkcbg1";
          con.ConnectionString = connectstring;
            command = new SqlCommand();
        }
        public XmlReader xml(string commands)
        {
            OpenConnection();
            command.CommandText = commands;
            command.CommandType = CommandType.StoredProcedure;
            XmlReader rea = command.ExecuteXmlReader();
            return rea;
        }
        private void OpenConnection()
        {
            if (con.State==ConnectionState.Closed)
            {
                con.Open();
                command.Connection = con
                    ;
            }
        }
        public void Closeconnection()
        {
            if (con.State==ConnectionState.Open)
            {
                con.Close();
            }
        }
        /// <summary>
        /// excute procedute
        /// </summary>
        /// <param name="commandString">comand string</param>
        /// <param name="list">parameter</param>
        /// <returns></returns>
        public DataTable ExcuteProcedute(string commandString,List<SqlParameter>list)
        {
           try
           {
               OpenConnection();
               command.CommandText = commandString;
               command.CommandType = CommandType.StoredProcedure;

               
              if (list!=null)
              {
                  
                      command.Parameters.Clear();
                 
                  foreach (SqlParameter para in list)
                  {
                      command.Parameters.Add(para);
                  }
               
              }
               SqlDataAdapter adappter = new SqlDataAdapter(command);
               DataTable data = new DataTable();
               adappter.Fill(data);
               return data;
           }
           catch (System.Exception ex)
           {
           	 throw ex;
           }
           finally
           {
               Closeconnection();
           }

        }
        public int ExcuteProceduteCommand(string commandString,List<SqlParameter>listPara)
        {
            try
            {
                OpenConnection();
                command.CommandText = commandString;
                command.CommandType = CommandType.StoredProcedure;
                if (command.Parameters.Count>0)
                {
                    command.Parameters.Clear();
                }
                foreach (SqlParameter sql in listPara)
                {
                    command.Parameters.Add(sql);
                }
               return command.ExecuteNonQuery()>0?1:0;
            }
            catch (System.Exception ex)
            {
            	throw ex;
            }
            finally
            {
                Closeconnection();
            }
        }
        public DataTable SelectAll(string commandString)
        {
           try
           {
               OpenConnection();
               command.CommandText = commandString;
               command.CommandType = CommandType.StoredProcedure;
               SqlDataAdapter adapter = new SqlDataAdapter(command);
               DataTable data = new DataTable();
               adapter.Fill(data);
               return data;
           }
           catch (System.Exception ex)
           {
           	  throw ex;
           }
           finally
           {
               Closeconnection();
           }
            
        }
        public SqlDataReader GetAll(string commandString)
        {
            try
            {
                OpenConnection();
                command.CommandText = commandString;
                command.CommandType = CommandType.StoredProcedure;
                SqlDataReader data = command.ExecuteReader();
                return data;
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            //finally
            //{
            //    Closeconnection();
            //}

        }
        public void Insert(byte[] ImageData,string Path)
        {
            OpenConnection();
            command.CommandText = @"[dbo].[spInsertImage]";
            command.CommandType = CommandType.StoredProcedure;
            command.Connection = con;
            command.Parameters.AddWithValue("@imagepath", Path);
            command.Parameters.AddWithValue("@imagedata", ImageData);
            try
            {
                int kiemtra = command.ExecuteNonQuery();
                if (kiemtra > 0)
                {
                    Error = string.Empty;
                }
            }
            catch (System.Exception ex)
            {
                Error = ex.Message;
            }
            finally
            {
                Closeconnection();
            }
            
        }
        public List<string>GetAllPath()
        {
            try
            {
                List<string> paths = new List<string>();
                OpenConnection();
                command.CommandText = @"[dbo].[spGetImage]";
                command.Connection = con;
                command.CommandType = CommandType.StoredProcedure;
                DataTable datatable = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(command);
                da.Fill(datatable);
                foreach (DataRow row in datatable.Rows)
                {
                    paths.Add(row["imagepath"].ToString());
                }
                return paths;
            }
            catch (System.Exception ex)
            {
                throw new FaultException(ex.Message) ;
            }
            finally
            {
                Closeconnection();
            }
        }
        public byte[] GetImage(string Path)
        {
            MemoryStream stream=new MemoryStream();
            
           try
           {
               OpenConnection();
               command.Parameters.Clear();
               command.CommandText = "dbo.spGetImageByPath";
               command.Parameters.AddWithValue("@Path", Path);
               command.CommandType = CommandType.StoredProcedure;
               command.Connection = con;

              
               SqlDataReader data = command.ExecuteReader();

               if (data.Read())
               {
               byte[]  dataimage = (byte[])data["imagedata"];
               return dataimage;
               }
               Error = string.Empty;
               return null;
           }
           catch (System.Exception ex)
           {
               Error = ex.Message;
               throw;
               
           }
           finally
           {
               Closeconnection();
           }
        }
    }
}
